use std::str::FromStr;

use crate::prelude::{get_app_config, get_my_pool, get_redis_cli, FieldProps, FieldType};
use aho_corasick::AhoCorasick;
use chrono::{Datelike, Days, NaiveDate};
use once_cell::sync::Lazy;
use redis::Commands;
use sqlx::Executor;
use sqlx::Row;
static SYDT_HKEY: &'static str = "sys_date";
use super::SourceType;
static AHO_MAP: Lazy<dashmap::DashMap<String, AhoCorasick>> = Lazy::new(|| dashmap::DashMap::new());
/// 解析sql,替换参数
fn sql_parse(sqls: &str, params_names: &[&str], params_vals: &[&str]) -> anyhow::Result<String> {
    let name = params_names.join("|");
    if !AHO_MAP.contains_key(&name) {
        let ac = AhoCorasick::builder().build(params_names).unwrap();
        AHO_MAP.insert(name.clone(), ac);
    }
    let ac = AHO_MAP.get(&name).unwrap();
    let mut wtr = vec![];
    ac.try_stream_replace_all(sqls.as_bytes(), &mut wtr, params_vals)
        .expect("字符串替换失败");
    let sql = String::from_utf8(wtr)?;
    Ok(sql)
}
pub async fn exec_sqls(
    sqls: &str,
    params_names: &[&str],
    params_vals: &[&str],
    tx: &mut sqlx::Transaction<'_, sqlx::MySql>,
) -> anyhow::Result<()> {
    let sqls = sql_parse(sqls, params_names, params_vals)?;
    let vec: Vec<&str> = sqls.split(';').collect();
    for sql in vec {
        let sql = sql.trim();
        if sql.is_empty() {
            continue;
        }
        tx.execute(sql).await?;
    }
    Ok(())
}

//ImportInfo 导入信息
pub struct ImportInfo {
    pub source_type: SourceType,
    pub data_date: NaiveDate,
}
//获取系统信息
pub async fn get_import_info() -> anyhow::Result<Vec<ImportInfo>> {
    let rows = sqlx::query("select datp,sydt from conf_sydt")
        .fetch_all(get_my_pool())
        .await?;
    let mut res: Vec<ImportInfo> = Vec::new();
    for row in rows {
        let datp: String = row.try_get(0)?;
        let data_date: NaiveDate = row.try_get(1)?;
        let source_type: SourceType = SourceType::from_str(&datp)?;
        res.push(ImportInfo {
            source_type,
            data_date,
        });
    }
    return Ok(res);
}
//根据类型获取系统日期
pub async fn get_sys_date(source_type: SourceType) -> anyhow::Result<NaiveDate> {
    let rcli = get_redis_cli();
    let mut rcon = rcli.get_connection()?;
    let is_ex: bool = rcon.exists(SYDT_HKEY)?;
    if !is_ex {
        let res = get_import_info().await?;
        for item in res {
            let _: () = rcon.hset(
                SYDT_HKEY,
                item.source_type.to_string(),
                item.data_date.format("%Y%m%d").to_string(),
            )?;
        }
    }
    let sys_date_str: String = rcon.hget(SYDT_HKEY, source_type.to_string())?;
    let sys_date = NaiveDate::parse_from_str(&sys_date_str, "%Y%m%d")?;
    return Ok(sys_date);
}

pub async fn get_back_date() -> anyhow::Result<NaiveDate> {
    let row = sqlx::query("select bday,bdtp from conf_updt")
        .fetch_one(get_my_pool())
        .await?;
    let bday: u32 = row.try_get(0)?;
    let bdtp: u8 = row.try_get(1)?;
    let ods_date = get_sys_date(SourceType::ODS).await?;
    if bdtp == 2 && bday > 30 {
        return Err(anyhow::anyhow!(
            "获取回溯日期失败,表conf_updt的字段bdtp为2:按月追溯时，bday不能大于30"
        ));
    } else if bdtp == 3 && bday > 90 {
        return Err(anyhow::anyhow!(
            "获取回溯日期失败,表conf_updt的字段bdtp为3:按季度追溯时，bday不能大于90"
        ));
    } else if bdtp == 3 && bday > 360 {
        return Err(anyhow::anyhow!(
            "获取回溯日期失败,表conf_updt的字段bdtp为4:按年度追溯时，bday不能大于360"
        ));
    }
    let bday = bday as u64;
    let bdate = if bdtp == 1 {
        ods_date.checked_sub_days(Days::new(bday))
    } else if bdtp == 2 {
        let bdate = ods_date.checked_sub_days(Days::new(bday)).unwrap();
        NaiveDate::from_ymd_opt(bdate.year(), bdate.month() - 1, 1)
    } else if bdtp == 3 {
        let bdate = ods_date.checked_sub_days(Days::new(bday)).unwrap();
        let m = bdate.month();
        let m2 = if m <= 3 {
            1
        } else if m <= 6 {
            4
        } else if m <= 9 {
            7
        } else {
            10
        };
        NaiveDate::from_ymd_opt(bdate.year(), m2, 1)
    } else if bdtp == 4 {
        let bdate = ods_date.checked_sub_days(Days::new(bday)).unwrap();
        NaiveDate::from_ymd_opt(bdate.year() - 1, bdate.month(), 1)
    } else {
        None
    };
    let bdate = bdate.ok_or(anyhow::anyhow!(
        "获取回溯日期失败,表conf_updt的字段bdtp只能是1:天,2:月,3:季度,4:年度；请检查"
    ))?;
    Ok(bdate)
}
pub async fn get_columns(table_name: &str) -> anyhow::Result<Vec<FieldProps>> {
    let mut schema = get_app_config().mysql.database.as_str();
    let table_name=if table_name.contains('.') {
        let parts: Vec<&str> = table_name.split('.').collect();
        if parts.len() == 2 {
            schema = parts[0];
        }
        parts[1]
    }else{
        table_name
    };
    let pool = get_my_pool();
    let sql_str = format!(
        r#"select COLUMN_NAME,COLUMN_COMMENT,COLUMN_TYPE,COLUMN_KEY,EXTRA,IS_NULLABLE,COLUMN_DEFAULT
from information_schema.COLUMNS where TABLE_SCHEMA='{}' and table_name = '{}'
order by ORDINAL_POSITION"#,
        schema, table_name
    );
    let rows = sqlx::query(&sql_str).fetch_all(pool).await?;
    let mut fields: Vec<FieldProps> = Vec::with_capacity(rows.len());
    for row in rows.iter() {
        let name: String = row.try_get(0)?;
        let comm_vec: Vec<u8> = row.try_get(1)?;
        let ctype_vec: Vec<u8> = row.try_get(2)?;
        let ckey_vec: Vec<u8> = row.try_get(3)?;
        let extra: String = row.try_get(4)?;
        let is_null: String = row.try_get(5)?;
        let defa_vec: Option<Vec<u8>> = row.try_get(6)?;
        let mut col_type = String::from_utf8(ctype_vec)?;
        if !col_type.ends_with(")") {
            let start=col_type.find('(');
            let end=col_type.find(')');
            if start.is_some() || end.is_some() {
                let start=start.unwrap_or(0);
                let end=end.unwrap_or(0);
                if end> start {
                    col_type = col_type.replace(&col_type[start..end+1], "");
                }
            }
        }
        let mut type_and_len = col_type.split('(');
        let type_name = type_and_len.next().unwrap_or(&col_type).to_uppercase();
        let field_type = match type_name.as_str() {
            "TINYINT" => {
                let len = type_and_len
                    .next()
                    .unwrap_or(&col_type)
                    .trim_end_matches(')')
                    .parse::<u8>().unwrap_or(0);
                if len == 1 {
                    FieldType::Bool
                } else {
                    FieldType::Int8
                }
            }
            "SMALLINT" | "YEAR" => FieldType::Int16,
            "MEDIUMINT" | "INT" => FieldType::Int32,
            "BIGINT" => FieldType::Int64,
            "TINYINT UNSIGNED" => FieldType::UInt8,
            "SMALLINT UNSIGNED" => FieldType::UInt16,
            "MEDIUMINT UNSIGNED"|"INT UNSIGNED"=> FieldType::UInt32,
            "BIGINT UNSIGNED" => FieldType::UInt64,
            "FLOAT" => FieldType::Float32,
            "DOUBLE" => FieldType::Float64,
            "DECIMAL" => {
                let mut ps = type_and_len
                    .next()
                    .unwrap_or("")
                    .trim_end_matches(')').split(",");
                let p=ps.next().unwrap_or("0").parse::<u8>()?;
                let s=ps.next().unwrap_or("0").parse::<u8>()?;
                FieldType::Decimal(p, s)
            }
            "DATE" => FieldType::Date,
            "DATETIME" | "TIMESTAMP" => FieldType::Datetime,
            "CHAR" | "VARCHAR" => {
                let len:u16 = type_and_len
                    .next()
                    .unwrap_or(&col_type)
                    .trim_end_matches(')')
                    .parse::<u16>()?;
                FieldType::String(len)
            },
            _ => anyhow::bail!("不支持的类型:{}", type_name),
        };
        let comment = String::from_utf8(comm_vec)?;
        let default = if let Some(defa)=defa_vec {
            Some(String::from_utf8(defa)?)
        }else{
            None
        };
        let col_key = String::from_utf8(ckey_vec)?;
        let f = FieldProps {
            name,
            field_type,
            is_pk: col_key.as_str()=="PRI",
            is_null: is_null.as_str()=="YES",
            is_auto_incr: extra.as_str()=="auto_increment",
            comment,
            default,
        };
        fields.push(f);
    }
    Ok(fields)
}


